package com.lovo.bbs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;

import javax.naming.NamingException;

import com.lovo.bbs.po.TopicPo;
import com.lovo.bbs.util.DbUtil;
import com.lovo.bbs.util.MysqlDb;
import com.lovo.bbs.util.StringUtil;

/**
 * 主题Dao
 * 
 * @author tiancen2001
 * 
 */
public class TopicDao {
	// 操作类型
	public static final int OPER_OF_UPDATE = 2;
	public static final int OPER_OF_DELETE = 3;
	
	public  TopicDao(){
		
	}

	/**
	 * 返回指定论坛下的主题Po列表
	 * 
	 * @param queryPage
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<TopicPo> getTopics(int forumid, int queryPage)
			throws NamingException, SQLException {
		String sqlPatch = null;
		if (forumid == 0) {// 当forumid==0时,等效于搜索所有的主题
			sqlPatch = "";
		} else {
			sqlPatch = " where topic.forumid=" + forumid;
		}
		// 数据库分页边界
		int startIndex = (queryPage - 1) * 20;
		int endIndex = (queryPage - 1) * 20 + 20;
		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name,topic.content,forum.forumname,   "
				+ "a.topicnum   "

				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid   "
				+ sqlPatch
				+ "    order by topic.topicdate desc,topicid  desc  limit  "
				+ startIndex + "  ,  " + endIndex;

		return queryTopics(sql);
	}

	/**
	 * 返回指定论坛下的置顶主题Po列表
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<TopicPo> getTopTopics(int forumid)
			throws NamingException, SQLException {

		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name, topic.content,forum.forumname,  "
				+ "a.topicnum   "
				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid   "
				+ "where  topic.forumid="
				+ forumid
				+ "      and  topic.istop='1'    order by topic.topicdate desc,topicid  desc";
		return queryTopics(sql);
	}

	/**
	 * 返回指定ID的Topic po
	 * 
	 * @param topicid
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  TopicPo getTopicInfo(int topicid) throws NamingException,
			SQLException {

		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name,topic.content,forum.forumname,  "
				+ "a.topicnum   "
				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid   "
				+ "where  topic.topicid=" + topicid;

		ArrayList<TopicPo> topicPos = queryTopics(sql);
		Iterator<TopicPo> it = topicPos.iterator();
		TopicPo po = it.next();

		return po;
	}

	/**
	 * 查询主题列表
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	private  ArrayList<TopicPo> queryTopics(String sql)
			throws NamingException, SQLException {
		ArrayList<TopicPo> topicPos = new ArrayList<TopicPo>();

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		ResultSet rs = con.prepareStatement(sql).executeQuery();
		while (rs.next()) {
			TopicPo po = new TopicPo();

			po.setTopicid(rs.getInt(1));
			po.setForumid(rs.getInt(2));

			po.setTopicTitle(rs.getString(3));
			po.setTopicDate(StringUtil.NoNull(rs.getString(4)));

			po.setHits(rs.getInt(5));
			po.setPostNum(rs.getInt(6));

			po.setHighlight(rs.getInt(7) == 1 ? true : false);
			po.setBest(rs.getInt(8) == 1 ? true : false);
			po.setTop(rs.getInt(9) == 1 ? true : false);
			po.setAttach(rs.getInt(10) == 1 ? true : false);

			po.setAuthorid(rs.getInt(11));
			po.setAuthorname(rs.getString(12));
			po.setAuthorScore(rs.getInt(13));
			po.setAuthorSignature(rs.getString(14));
			po.setAuthorRankid(rs.getInt(15));
			po.setAuthorHeadimg(rs.getString(16));

			po.setLastPostDate(StringUtil.NoNull(rs.getString(17)));
			po.setLastPostAuthor(StringUtil.NoNull(rs.getString(18)));

			po.setContent(StringUtil.NoNull(rs.getString(19)));

			po.setForumName(StringUtil.NoNull(rs.getString(20)));

			po.setUserTopicNum(rs.getInt(21));

			topicPos.add(po);
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);

		return topicPos;
	}

	/**
	 * 新增主题
	 * 
	 * @param po
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int addOneTopic(TopicPo po) throws NamingException,
			SQLException {
		int topicid = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String insertSql = "insert into topic"
				+ " (forumid, topictitle, authorid, topicdate,content) "
				+ "values(?,?,(select userid from user where username=?),now(),?)";
		PreparedStatement ps = con.prepareStatement(insertSql);
		ps.setInt(1, po.getForumid());
		ps.setString(2, po.getTopicTitle());
		ps.setString(3, po.getAuthorname());
		ps.setString(4, po.getContent());
		int added = ps.executeUpdate();
		if (added == 1) {
			topicid = getMaxTopicid();
		}
		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);

		return topicid;
	}

	/**
	 * 返回最大主题ID
	 * 
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int getMaxTopicid() throws NamingException, SQLException {
		int topicid = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		ResultSet rs = con.prepareStatement("select max(topicid) from topic")
				.executeQuery();
		if (rs.next()) {
			topicid = rs.getInt(1);
		} else {
			topicid = 1;
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return topicid;
	}

	/**
	 * 查看数加1
	 * 
	 * @param topicid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int topicHitted(int topicid) throws NamingException,
			SQLException {
		int added = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update topic set hits=hits+1 where  topicid=" + topicid;
		added = con.prepareStatement(sql).executeUpdate();

		mysqldb.closeConnection(con);
		return added;
	}

	/**
	 * 指定主题新增一个回复
	 * 
	 * @param postid
	 * @param topicid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int addOnePost(int postid, int topicid)
			throws NamingException, SQLException {
		int added = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		String sql = "update topic set postnum=postnum+1,lastpostid=? "
				+ "   where  topicid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, postid);
		ps.setInt(2, topicid);
		added = ps.executeUpdate();

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
		return added;
	}

	/**
	 * 按给定条件搜索所有论坛下的主题
	 * 
	 * @param forumid
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  ArrayList<TopicPo> switchSearch(String cond, int forumid)
			throws NamingException, SQLException {
		String sqlPatch = null;

		if (cond.equals("best")) {
			sqlPatch = "topic.isbest='1'";
		} else if (cond.equals("top")) {
			sqlPatch = "topic.istop='1'";
		} else if (cond.equals("today")) {
			sqlPatch = "year(topicdate)=year(now())  and  month(topicdate)=month(now())  and day(topicdate)=day(now())";
		} else if (cond.equals("thisweek")) {
			sqlPatch = "year(topicdate)=year(now())  and  week(topic.topicdate)=week(now())";
		} else if (cond.equals("localbest")) {
			sqlPatch = "topic.forumid=" + forumid + "  and  topic.isbest='1' ";
		} else if (cond.equals("localhot")) {
			sqlPatch = "topic.forumid=" + forumid + "  and  topic.postnum>=10 ";
		}

		String sql = "select   "
				+ "topic.topicid, topic.forumid,"
				+ "topic.topictitle,topic.topicdate ,"
				+ "topic.hits, topic.postnum ,"
				+ "topic.ishighlight, topic.isbest, topic.istop, topic.isattach ,"

				+ "topic.authorid,a.username as topic_author_name,"
				+ "a.score as author_score,a.signature as author_signature,"
				+ "a.rankid as author_rankid,userhead.headimg as author_headimg,"

				+ "post.postdate as last_post_postdate,"
				+ "b.username as last_post_author_name,topic.content,forum.forumname,  "
				+ "a.topicnum   "
				+ "from topic left join user a on topic.authorid=a.userid   "
				+ "left join userhead  on a.headimgid=userhead.headid   "
				+ "left join post on topic.lastpostid=post.postid   "
				+ "left join user b on post.userid=b.userid   "
				+ "left join forum on topic.forumid=forum.forumid    "
				+ "where  " + sqlPatch
				+ "    order by topic.topicdate desc,topicid  desc ";

		ArrayList<TopicPo> topicPos = queryTopics(sql);
		return topicPos;
	}

	/**
	 * 查询指定论坛的主题数 参数为0时查询所有论坛主题总数
	 * 
	 * @param forumid
	 * @return
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getTopicNum(int forumid) throws NamingException,
			SQLException {
		int topicNum = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sqlPatch = null;
		if (forumid == 0) {
			sqlPatch = "";
		} else {
			sqlPatch = "where forumid=" + forumid;
		}

		String sql = "select count(*) from topic   " + sqlPatch;

		ResultSet rs = con.prepareStatement(sql).executeQuery();
		if (rs.next()) {
			topicNum = rs.getInt(1);
		}
		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return topicNum;
	}

	/**
	 * 更新主题表
	 * 
	 * @param po
	 * @param operType
	 * @return
	 * @throws NamingException
	 * @throws SQLException
	 */
	public  int updateTopic(TopicPo po, int operType)
			throws NamingException, SQLException {
		int updated = 0;
		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);
		con.setAutoCommit(false);

		if (operType == TopicDao.OPER_OF_UPDATE) {
			String sqlUpdate = "update topic set  forumid=?,topictitle=?,hits=?,postnum=?,"
					+ "ishighlight=?,isbest=?,istop=?  where topicid=?";
			PreparedStatement ps = con.prepareStatement(sqlUpdate);

			ps.setInt(1, po.getForumid());
			ps.setString(2, po.getTopicTitle());
			ps.setInt(3, po.getHits());
			ps.setInt(4, po.getPostNum());

			ps.setString(5, po.isHighlight() ? "1" : "0");
			ps.setString(6, po.isBest() ? "1" : "0");
			ps.setString(7, po.isTop() ? "1" : "0");

			ps.setInt(8, po.getTopicid());

			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		} else if (operType == TopicDao.OPER_OF_DELETE) {
			String sqlDelete = "delete from topic where topicid=?";
			PreparedStatement ps = con.prepareStatement(sqlDelete);
			ps.setInt(1, po.getTopicid());

			updated = ps.executeUpdate();
			mysqldb.closePS(ps);
		}
		con.commit();
		mysqldb.closeConnection(con);
		return updated;
	}

	/**
	 * 主题回复数减一
	 * 
	 * @param topicid
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int delonePost(int topicid) throws NamingException,
			SQLException {
		int updated = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "update topic set postnum=postnum-1 where topicid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, topicid);
		updated = ps.executeUpdate();

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
		return updated;
	}

	/**
	 * 计算主题最后回复
	 * 
	 * @param topicid
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int setLastPost(int topicid) throws NamingException,
			SQLException {
		int setted = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "update  topic set lastpostid=("
				+ "select postid from post  where topicid=? order by  postdate desc limit 1"
				+ ") where topicid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, topicid);
		ps.setInt(2, topicid);

		setted = ps.executeUpdate();

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
		return setted;
	}

	/**
	 * 删除该用户的主题
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int delTopicByUserID(int userid) throws NamingException,
			SQLException {
		int del = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "delete from topic where authorid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, userid);
		del = ps.executeUpdate();

		mysqldb.closePS(ps);
		mysqldb.closeConnection(con);
		return del;
	}

	/**
	 * 计算主题的回复数
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int calcPostNum(int topicid) throws NamingException,
			SQLException {
		int calced = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "update topic set postnum="
				+ "(select count(*)  from post where topicid=" + topicid
				+ ")    where  topicid=" + topicid;

		calced = con.prepareStatement(sql).executeUpdate();

		mysqldb.closeConnection(con);
		return calced;
	}

	/**
	 * 返回该主题的论坛ID
	 * 
	 * @throws SQLException
	 * @throws NamingException
	 */
	public  int getForumID(int topicid) throws NamingException,
			SQLException {
		int forumid = 0;

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		String sql = "select forumid from topic where topicid=" + topicid;
		ResultSet rs = con.prepareStatement(sql).executeQuery();
		if (rs.next()) {
			forumid = rs.getInt(1);
		}

		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return forumid;
	}

	/**
	 * 删除该用户的主题,返回主题所属的论坛
	 */
	public  HashSet<Integer> delTopics(int userid)
			throws NamingException, SQLException {
		HashSet<Integer> set = new HashSet<Integer>();

		MysqlDb mysqldb = new MysqlDb();
		mysqldb.setPool(true);
		Connection con = DbUtil.getConn(mysqldb);

		// 查询该用户的主题所属的论坛
		String sqlGetForumID = "select distinct(forumid)  from topic    where  topic.authorid="
				+ userid;
		ResultSet rs = con.prepareStatement(sqlGetForumID).executeQuery();
		while (rs.next()) {
			set.add(rs.getInt(1));
		}

		// 删除该用户的主题
		String sqlDel = "delete from topic where  authorid=" + userid;
		con.prepareStatement(sqlDel).executeUpdate();

		mysqldb.closeRS(rs);
		mysqldb.closeConnection(con);
		return set;
	}
}
